import requests
import pymysql
import lxml.html
import re
import time

class DateStatus:

    def __init__(self):
        print("inited!")

    def getList(self):
        conn = pymysql.connect(host='192.168.187.130', user='root', passwd='123456', db='homework')
        cur = conn.cursor()
        try:
            sql = "truncate table timestatus"
            cur.execute(sql)
            conn.commit()
            print("table is cleaned!")

            sql = "select `status` from project group by `status`"
            cur.execute(sql)
            statuslist = cur.fetchall()
            print("status:")
            for ll in statuslist:
                print(ll[0])

            sql = 'select date_format(date,"%Y-%m") from project group by date_format(date,"%Y-%m")'
            cur.execute(sql)
            timelist = cur.fetchall()
            print("time:")
            for ll in timelist:
                print(ll[0])

            for tl in timelist:
                for sl in statuslist:
                    tm = tl[0]
                    st = sl[0]

                    #获取数据
                    #print("tm:"+str(type(tm)))
                    #print("st:"+str(type(st)))
                    #sql = "select count(pid) from project where date_format(date,"%'+'Y-%'+'m") = "%s" and `status` = "%s"' % (tm,st)
                    sql = "select count(pid) from project where date_format(date,\"%"+"Y-%"+"m\") = '%s' and status = '%s'" % (tm,st)
                    print("sql:"+sql)
                    cur.execute(sql)
                    num = cur.fetchone()[0]

                    #录入数据
                    sql = "insert into timestatus(time,status,sum) values('%s','%s',%d)" %(tm,st,num)
                    print("sql:"+sql)
                    cur.execute(sql)
                    conn.commit()
        except:
            print("")
        cur.close()
        conn.close()

if __name__ in "__main__":
    cls = DateStatus()
    cls.getList()
